package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;
import com.mbc.rmt.data.CashRemittanceByAgentDataList;
import com.mbc.rmt.entities.CashRemittanceByAgent;

public class CashRemittanceByAgentArchiveDAOImpls extends DAO<CashRemittanceByAgent> implements CashRemittanceByAgentArchiveDAO{

	public CashRemittanceByAgentArchiveDAOImpls(Connection conn){
		this.conn = conn;
		this.table_name = "CashRemittanceByAgent_Archive";
	}
	@Override
	public void create(CashRemittanceByAgent entity) throws SQLException {
		query = "insert into " 
				+ table_name 
				+ "(ReceiptID,ReceiptNo,CreatedDate,ReceivedDate,DefaultBranchID," 
				+ " CreatedBy,FromAgentID,PaymentCurrencyID,ReceivedCurrencyID,"
				+ " Rate,TotalPaymentAmount,TotalReceivedAmount,ExtraFees," 
				+ " Received,BankID,Status,TransactionType) values (  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getReceiptId());
		pstmt.setString(i++, entity.getReceiptNo());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getCreatedDate().getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getReceivedDate().getTime()));
		pstmt.setLong(i++, entity.getDefaultBranchId());
		if(entity.getCreatedBy() != null)
			pstmt.setLong(i++, entity.getCreatedBy());
		else
			pstmt.setString(i++, null);
		if(entity.getFromAgentId() != null)
			pstmt.setLong(i++, entity.getFromAgentId());
		else
			pstmt.setString(i++, null);
		if(entity.getPaymentCurrencyId() != null)
			pstmt.setLong(i++, entity.getPaymentCurrencyId());
		else
			pstmt.setString(i++, null);
		if(entity.getReceivedCurrencyId() != null)
			pstmt.setLong(i++, entity.getReceivedCurrencyId());
		else
			pstmt.setString(i++, null);
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getTotalPaymentAmt());
		pstmt.setDouble(i++, entity.getTotalReceivedAmt());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setDouble(i++, entity.getReceived());
		if(entity.getBankId() != null)
			pstmt.setLong(i++, entity.getBankId());
		else
			pstmt.setString(i++, null);
		pstmt.setByte(i++ , entity.getStatus());
		pstmt.setInt(i++, entity.getTransactionType());
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void update(CashRemittanceByAgent entity) throws SQLException {
		query = "update "
				+table_name
				+ " set ReceivedDate = ?," 
				+ " DefaultBranchID = ?,CreatedBy = ?,FromAgentID = ?,PaymentCurrencyID = ?," 
				+ " ReceivedCurrencyID = ?,"
				+ " Rate = ?,TotalPaymentAmount = ?,TotalReceivedAmount = ?,ExtraFees = ?," 
				+ " Received = ?,BankID = ?, Status = ?, TransactionType = ?"
				+ " where ReceiptID = "+entity.getReceiptId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getReceivedDate().getTime()));
		pstmt.setLong(i++, entity.getDefaultBranchId());
		if(entity.getCreatedBy() != null)
			pstmt.setLong(i++, entity.getCreatedBy());
		else
			pstmt.setString(i++, null);
		if(entity.getFromAgentId() != null)
			pstmt.setLong(i++, entity.getFromAgentId());
		else
			pstmt.setString(i++, null);
		if(entity.getPaymentCurrencyId() != null)
			pstmt.setLong(i++, entity.getPaymentCurrencyId());
		else
			pstmt.setString(i++, null);
		if(entity.getReceivedCurrencyId() != null)
			pstmt.setLong(i++, entity.getReceivedCurrencyId());
		else
			pstmt.setString(i++, null);
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getTotalPaymentAmt());
		pstmt.setDouble(i++, entity.getTotalReceivedAmt());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setDouble(i++, entity.getReceived());
		if(entity.getBankId() != null)
			pstmt.setLong(i++, entity.getBankId());
		else
			pstmt.setString(i++, null);
		pstmt.setByte(i++ , entity.getStatus());
		pstmt.setInt(i++, entity.getTransactionType());
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where ReceiptID = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public CashRemittanceByAgentDataList findCRByAgent(CriteriaParams cri)
			throws SQLException {
		CashRemittanceByAgentDataList l_DataList = new CashRemittanceByAgentDataList();
		ArrayList<CashRemittanceByAgent> l_entityList = new ArrayList<CashRemittanceByAgent>();
		
		String l_cri = " and (cra.ReceiptNo like N'%" + cri.getKeyword() + "%'"
				+ " or cra.FromAgentID like '%" + cri.getKeyword() + "%'"
				+ " or cra.PaymentCurrencyID like '%" + cri.getKeyword() + "%'"
				+ " or cra.ReceivedCurrencyID like '%" + cri.getKeyword()
				+ "%')" + " and cra.DefaultBranchID = " + cri.getBranchId();

		query = "set rowcount "
				+ cri.getSize()
				+ " select * from(select  ROW_NUMBER()  over( order by cra.ReceiptID desc)No, "
				+ " cra.*, a.agent_code, a.name "
				+ " from CashRemittanceByAgent cra "
				+ " left join Agent a on a.agent_id= cra.FromAgentID "
				+ " left join Currency cp on cp.CurrencyId=cra.PaymentCurrencyID "
				+ " left join Currency cr on cr.CurrencyId=cra.ReceivedCurrencyID "
				+ " where 1 = 1" + l_cri + ") " + table_name + " where No > "+cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_entityList.add(readRecord(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(ReceiptID) from " + table_name + " cra where 1 = 1"
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public CashRemittanceByAgent find(long key) throws SQLException {
		CashRemittanceByAgent l_crByAgent = null;
		query = "select cra.*,a.agent_code,a.name,b.BranchCode,c.CurrencyCode PCurrency," +
				" cc.CurrencyCode RCurrency,ba.BankCode from CashRemittanceByAgent_Archive cra " +
				" left join Agent a on a.agent_id = cra.FromAgentID " +
				" left join Branch b on b.BranchId = cra.DefaultBranchID " +
				" left join Currency c on c.CurrencyId = cra.PaymentCurrencyID " +
				" left join Currency cc on cc.CurrencyId = cra.ReceivedCurrencyID " +
				" left join Bank ba on ba.BankId = cra.BankID " +
				" where ReceiptID = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_crByAgent = readRecords(rs);
		rs.close();
		pstmt.close();
		return l_crByAgent;
	}

	private CashRemittanceByAgent readRecords(ResultSet rs)throws SQLException {
		CashRemittanceByAgent l_entity = new CashRemittanceByAgent();
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setDefaultBranchId(rs.getLong("DefaultBranchID"));
		//l_entity.setCreatedBy(rs.getLong("UserID"));
		l_entity.setFromAgentId(rs.getLong("FromAgentID"));
		l_entity.setPaymentCurrencyId(rs.getLong("PaymentCurrencyID"));
		l_entity.setReceivedCurrencyId(rs.getLong("ReceivedCurrencyID"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setTotalPaymentAmt(rs.getDouble("TotalPaymentAmount"));
		l_entity.setTotalReceivedAmt(rs.getDouble("TotalReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setReceived(rs.getDouble("Received"));
		l_entity.setBankId(rs.getLong("BankID"));
		l_entity.setFromAgentCode(rs.getString("agent_code"));
		l_entity.setFromAgentName(rs.getString("name"));
		l_entity.setPaymentCurrencyCode(rs.getString("PCurrency"));
		l_entity.setReceivedCurrencyCode(rs.getString("RCurrency"));
		l_entity.setBankCode(rs.getString("BankCode"));
		return l_entity;
	}
	@Override
	protected CashRemittanceByAgent readRecord(ResultSet rs)
			throws SQLException {
		CashRemittanceByAgent l_entity = new CashRemittanceByAgent();
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setDefaultBranchId(rs.getLong("DefaultBranchID"));
		l_entity.setCreatedBy(rs.getLong("UserID"));
		l_entity.setFromAgentId(rs.getLong("FromAgentID"));
		l_entity.setPaymentCurrencyId(rs.getLong("PaymentCurrencyID"));
		l_entity.setReceivedCurrencyId(rs.getLong("ReceivedCurrencyID"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setTotalPaymentAmt(rs.getDouble("TotalPaymentAmount"));
		l_entity.setTotalReceivedAmt(rs.getDouble("TotalReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setReceived(rs.getDouble("Received"));
		l_entity.setBankId(rs.getLong("BankID"));
		l_entity.setFromAgentCode(rs.getString("Agent_Code"));
		l_entity.setFromAgentName(rs.getString("Name"));
		return l_entity;
	}
	@Override
	public CashRemittanceByAgentDataList findCRByAgentArchive(CriteriaParams cri)
			throws SQLException {
		CashRemittanceByAgentDataList l_DataList = new CashRemittanceByAgentDataList();
		ArrayList<CashRemittanceByAgent> l_entityList = new ArrayList<CashRemittanceByAgent>();
	
		String l_cri = " and (cra.ReceiptNo like N'%" + cri.getKeyword() + "%'"
				+ " or cra.FromAgentID like '%" + cri.getKeyword() 
				+ "%')" + " and cra.DefaultBranchID = " + cri.getBranchId() +
				" and cra.TransactionType = " + 1;

		query = "set rowcount "
				+ cri.getSize()
				+ " select * from(select  ROW_NUMBER()  over( order by cra.ReceiptNo desc)No, "
				+ " cra.*, a.agent_code, a.name "
				+ " from CashRemittanceByAgent_Archive cra "
				+ " left join Agent a on a.agent_id= cra.FromAgentID "
				+ " left join Currency cp on cp.CurrencyId=cra.PaymentCurrencyID "
				+ " left join Currency cr on cr.CurrencyId=cra.ReceivedCurrencyID "
				+ " where 1 = 1" + l_cri + ") " + table_name + " where No > "+cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_entityList.add(readRecordForArchive(rs));
		}
		
		rs.close();
		pstmt.close();
		l_DataList.setEntityList(l_entityList);
		query = "select count(ReceiptID) from " + table_name + " cra where 1 = 1"
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}	
	
	protected CashRemittanceByAgent readRecordForArchive(ResultSet rs)
			throws SQLException {
		CashRemittanceByAgent l_entity = new CashRemittanceByAgent();
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setDefaultBranchId(rs.getLong("DefaultBranchID"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setFromAgentId(rs.getLong("FromAgentID"));
		l_entity.setPaymentCurrencyId(rs.getLong("PaymentCurrencyID"));
		l_entity.setReceivedCurrencyId(rs.getLong("ReceivedCurrencyID"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setTotalPaymentAmt(rs.getDouble("TotalPaymentAmount"));
		l_entity.setTotalReceivedAmt(rs.getDouble("TotalReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setReceived(rs.getDouble("Received"));
		l_entity.setBankId(rs.getLong("BankID"));
		l_entity.setStatus(rs.getByte("Status"));
		l_entity.setTransactionType(rs.getInt("TransactionType"));
		l_entity.setFromAgentCode(rs.getString("Agent_Code"));
		l_entity.setFromAgentName(rs.getString("Name"));
		return l_entity;
	}
	@Override
	public List<CashRemittanceByAgent> findByDateRange(Date fromDate,
			Date toDate, long branchId) throws SQLException {
		ArrayList<CashRemittanceByAgent> l_CashRemittanceByAgentArchiveList = new ArrayList<CashRemittanceByAgent>();
		query = "select a.*, b.Code from " + table_name + " a"
				+ " inner join Addcode b on a.AddCodeId = b.AddCodeId"
				+ " where a.ReceivedDate >= ? and a.ReceivedDate <= ?";
		if (branchId != 0)
			query += " and a.BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		pstmt.setDate(1, new java.sql.Date(fromDate.getTime()));
		pstmt.setDate(2, new java.sql.Date(toDate.getTime()));
		rs = pstmt.executeQuery();
		while (rs.next()) {
			CashRemittanceByAgent l_CashRemittanceByAgentArchive = readRecord(rs);
			l_CashRemittanceByAgentArchive.setCode(rs.getString("Code"));
			l_CashRemittanceByAgentArchiveList
					.add(l_CashRemittanceByAgentArchive);
		}
		rs.close();
		pstmt.close();
		return l_CashRemittanceByAgentArchiveList;
	}
}
